CREATE OR REPLACE PACKAGE BODY pkg_logger AS
/*****************************************************************************
* Name : pkg_logger
* Type : Package body
* Description :  For logging progress and error messages of PL/SQL component
*
* Copyright : VocaLink
*
* Amendment History :
*
* Version  Date         Changed By     Reason (e.g. Initial, Change Ref)
******************************************************************************
* 1.0      01/08/2007  Rajeeva Tiwari        Commom Component
*****************************************************************************/

   PROCEDURE log_progress( p_package_name       IN t_progress_log.package_name%TYPE
                         , p_procedure_name     IN t_progress_log.procedure_name%TYPE
                         , p_progress_message   IN t_progress_log.progress_message%TYPE
                         , p_stage              IN t_progress_log.stage%TYPE DEFAULT NULL
                         , p_work_identifier    IN t_progress_log.work_identifier%TYPE DEFAULT NULL)
   IS
   /*****************************************************************************
    * Name : log_progress
    * Type : PROCEDURE
    * Description : -- log without breaking application transactions.
    *
    * Parameters In : package_name     = The package name for with the message is being logged
    *                 procedure_name   = The procedure name for with the message is being logged
    *                 progress_message = The message which will be logged.
    *
    * Parameters Out : None
    *
    * Amendment History :
    *
    * Version  Date         Changed By     Reason (e.g. Initial, Change Ref)
    ******************************************************************************
    * 1.0      12/04/2011  Rajeeva Tiwari        VocaLink Commom Component
    *****************************************************************************/
   PRAGMA AUTONOMOUS_TRANSACTION;
      v_logging_enabled CHAR(1) := 'N';
      v_app_session_id VARCHAR2(256):= '';
   BEGIN

     SELECT parameter_value
     INTO   v_logging_enabled
     FROM   t_parameter
     WHERE  parameter_name = 'LOGGING_ENABLED';

     SELECT SYS_CONTEXT ('USERENV', 'sessionid')
     INTO   v_app_session_id
     FROM   DUAL;

     IF v_logging_enabled = 'Y' THEN
        INSERT INTO t_PROGRESS_LOG
        ( PROGRESS_LOG_ID
        , PACKAGE_NAME
        , PROCEDURE_NAME
        , PROGRESS_MESSAGE
        , APP_SESSION_ID
        , stage
        , work_identifier
        )
        VALUES
        ( progress_log_id_seq.NEXTVAL
        , p_package_name
        , p_procedure_name
        , p_progress_message
        , v_app_session_id
        , p_stage
        , p_work_identifier
        );
     END IF;
     COMMIT;

   EXCEPTION
     WHEN OTHERS THEN
        ROLLBACK;
        NULL; -- what's the behavior if the audit log does not succeed?
        -- note rollback does not impact the rest of the processing
   END log_progress;

   PROCEDURE log_error( p_package_name       IN t_error_log.package_name%TYPE
                      , p_procedure_name     IN t_error_log.procedure_name%TYPE
                      , p_app_error_message  IN t_error_log.app_error_message%TYPE
                      , p_oracle_message     IN t_error_log.oracle_message%TYPE
                      , p_ems_code           IN t_error_log.ems_code%TYPE DEFAULT NULL)
   IS
   /*****************************************************************************
    * Name : log_error
    * Type : PROCEDURE
    * Description : -- log without breaking application transactions. log the error message.
    *
    * Parameters In : package_name     = The package name for with the message is being logged
    *                 procedure_name   = The procedure name for with the message is being logged
    *                 app_error_message = The actual application error message.
    *                 oracle_message    = the oracle error message.
    *
    * Parameters Out : None
    *
    * Amendment History :
    *
    * Version  Date         Changed By     Reason (e.g. Initial, Change Ref)
    ******************************************************************************
    * 1.0      12/04/2011  Rajeeva Tiwari        VocaLink Commom Component
    *****************************************************************************/
   PRAGMA AUTONOMOUS_TRANSACTION;
      v_app_session_id VARCHAR2(256):= '';
   BEGIN
       SELECT SYS_CONTEXT ('USERENV', 'sessionid')
       INTO    v_app_session_id
       FROM    DUAL;

       INSERT INTO t_ERROR_LOG
        ( ERROR_LOG_ID
        , PACKAGE_NAME
        , PROCEDURE_NAME
        , APP_ERROR_MESSAGE
        , ORACLE_MESSAGE
        , APP_SESSION_ID
        , EMS_CODE
        )
        VALUES
        ( error_log_id_seq.NEXTVAL
        , p_package_name
        , p_procedure_name
        , p_app_error_message
        , p_oracle_message
        , v_app_session_id
        , p_ems_code
        );
     COMMIT;

   EXCEPTION
     WHEN OTHERS THEN
        ROLLBACK;
        NULL; -- what's the behavior if the error log does not succeed?
        -- note rollback does not impact the rest of the processing
   END log_error;

END pkg_logger;
/
